# -*- coding: utf-8 -*-
# coding=utf-8
"""

@author: fuhaishan
"""

import os
import codecs
import sys

# from openpyxl.reader.excel import load_workbook
from openpyxl import load_workbook
reload(sys)
sys.setdefaultencoding('utf8')

"""
    解析文件的配置参数
"""
config = {
    'dbXlsxFileName': 'D:\\1.xlsx',
    'dbName': '',
    'sqlConfig': {
        'schema': 'postgres'
    },
    'insert_pre_str': '''INSERT INTO `mallcloud`.`bo_project`(`id`, `creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `user_id`, `project_name`, `project_introduce`, `video_url`, `pic_url`, `project_type_id`, `city_id`, `city_str`, `status`, `company`, `detail_url`, `project_money`) VALUES ('''
}

"""
    解析excel文件,基于openpyxl 2.6.2
"""


def analysisile():
    # region 1、加载xlsx文件，解析sheet

    xlsx_file = config['dbXlsxFileName']
    wb = load_workbook(xlsx_file)
    sheet_names = wb.sheetnames
    # endregion 1、加载xlsx文件，解析sheet

    print('start')
    # 生成的sql语句
    sql_str = []
    sql_comment_str = []

    print

    sheet = wb['Sheet1']
    max_row = sheet.max_row

    for sheet_row_index in range(0, max_row):

        if sheet_row_index < 2:
            continue

        # region bo_project sql语句生成
        bo_insert_sql_str = []
        # bo_insert_sql_str.append(config['insert_pre_str'])

        bo_project_id = str(sheet.cell(row=sheet_row_index, column=1).value)
        bo_insert_sql_str.append(bo_project_id)

        creator = "0"
        bo_insert_sql_str.append(creator)

        create_time = "NOW()"
        bo_insert_sql_str.append(create_time)

        modifier = "NULL"
        bo_insert_sql_str.append(modifier)

        modify_time = "NULL"
        bo_insert_sql_str.append(modify_time)

        delete_flag = "0"
        bo_insert_sql_str.append(delete_flag)

        remark = "NULL"
        bo_insert_sql_str.append(remark)

        user_id = "94"
        bo_insert_sql_str.append(user_id)

        project_name = "'" + sheet.cell(row=sheet_row_index, column=4).value + "'"
        bo_insert_sql_str.append(project_name)

        project_introduce = "'" + sheet.cell(row=sheet_row_index, column=5).value + "'"
        bo_insert_sql_str.append(project_introduce)

        video_url = "'http://47.116.71.76:23349/upload/bo/video/" + sheet.cell(row=sheet_row_index, column=3).value + "'"
        bo_insert_sql_str.append(video_url)

        pic_url = "NULL"
        bo_insert_sql_str.append(pic_url)

        # project_type_id = str(sheet.cell(row=sheet_row_index, column=4).value).split('-')[0]
        project_type_id = "1"
        bo_insert_sql_str.append(project_type_id)

        city_id = "21"
        bo_insert_sql_str.append(city_id)

        city_str = "'上海市'"
        bo_insert_sql_str.append(city_str)

        status = "0"
        bo_insert_sql_str.append(status)

        company = "'" + sheet.cell(row=sheet_row_index, column=8).value + "'"
        bo_insert_sql_str.append(company)

        detail_url = "'http://47.103.98.11:8683/#/" + str(sheet.cell(row=sheet_row_index, column=1).value) + "'"
        bo_insert_sql_str.append(detail_url)

        project_money = "'1.00000000'"
        bo_insert_sql_str.append(project_money)

        print(config['insert_pre_str'] + ','.join(bo_insert_sql_str) + ");")
        # endregion bo_project sql语句生成

        # region bo_project_finance sql语句生成

        # region 1、相关资讯
        insert_project_finance1 = []

        # creator
        insert_project_finance1.append("0")
        # create_time
        insert_project_finance1.append("NOW()")
        # modifier
        insert_project_finance1.append("NULL")
        # modify_time
        insert_project_finance1.append("NULL")
        # delete_flag
        insert_project_finance1.append("0")
        # remark
        insert_project_finance1.append("NULL")
        # project_id
        insert_project_finance1.append(bo_project_id)
        # finance_config_id
        insert_project_finance1.append("1")
        # content
        if sheet.cell(row=sheet_row_index, column=22).value:
            insert_project_finance1.append("'" + str(sheet.cell(row=sheet_row_index, column=22).value) + "'")
        else:
            insert_project_finance1.append("NULL")
        # extend_title
        insert_project_finance1.append("'相关资讯'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=23).value:
            insert_project_finance1.append("'http://47.116.71.76:23349/upload/bo/pic/" + str(sheet.cell(row=sheet_row_index, column=23).value).split(',')[0] + "'")
        else:
            insert_project_finance1.append("NULL")
        # image_url
        insert_project_finance1.append("1")

        # insert_project_finance1 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print('''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
              + ','.join(insert_project_finance1) + ");")
        # endregion 1、相关资讯

        # region 2、团队成员
        insert_project_finance2 = []

        # creator
        insert_project_finance2.append("0")
        # create_time
        insert_project_finance2.append("NOW()")
        # modifier
        insert_project_finance2.append("NULL")
        # modify_time
        insert_project_finance2.append("NULL")
        # delete_flag
        insert_project_finance2.append("0")
        # remark
        insert_project_finance2.append("NULL")
        # project_id
        insert_project_finance2.append(bo_project_id)
        # finance_config_id
        insert_project_finance2.append("2")
        # content
        if sheet.cell(row=sheet_row_index, column=26).value:
            insert_project_finance2.append("'" + str(sheet.cell(row=sheet_row_index, column=26).value) + "'")
        else:
            insert_project_finance2.append("NULL")
        # extend_title
        insert_project_finance2.append("'团队成员'")
        # image_url
        insert_project_finance2.append("NULL")
        # is_show
        insert_project_finance2.append("1")

        # insert_project_finance2 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print(
                    '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                    + ','.join(insert_project_finance2) + ");")
        # endregion 2、团队成员

        # region 3、项目优势
        insert_project_finance3 = []

        # creator
        insert_project_finance3.append("0")
        # create_time
        insert_project_finance3.append("NOW()")
        # modifier
        insert_project_finance3.append("NULL")
        # modify_time
        insert_project_finance3.append("NULL")
        # delete_flag
        insert_project_finance3.append("0")
        # remark
        insert_project_finance3.append("NULL")
        # project_id
        insert_project_finance3.append(bo_project_id)
        # finance_config_id
        insert_project_finance3.append("3")
        # content
        if sheet.cell(row=sheet_row_index, column=12).value:
            insert_project_finance3.append("'" + str(sheet.cell(row=sheet_row_index, column=12).value) + "'")
        else:
            insert_project_finance3.append("NULL")
        # extend_title
        insert_project_finance3.append("'项目优势'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=13).value:
            insert_project_finance3.append("'http://47.116.71.76:23349/upload/bo/pic/" + str(sheet.cell(row=sheet_row_index, column=13).value).split(',')[0] + "'")
        else:
            insert_project_finance3.append("NULL")

        # image_url
        insert_project_finance3.append("1")

        # insert_project_finance3 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print(
                    '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                    + ','.join(insert_project_finance3) + ");")
        # endregion 3、项目优势

        # region 4、项目进展
        insert_project_finance4 = []

        # creator
        insert_project_finance4.append("0")
        # create_time
        insert_project_finance4.append("NOW()")
        # modifier
        insert_project_finance4.append("NULL")
        # modify_time
        insert_project_finance4.append("NULL")
        # delete_flag
        insert_project_finance4.append("0")
        # remark
        insert_project_finance4.append("NULL")
        # project_id
        insert_project_finance4.append(bo_project_id)
        # finance_config_id
        insert_project_finance4.append("4")
        # content
        if sheet.cell(row=sheet_row_index, column=20):
            insert_project_finance4.append("'" + str(sheet.cell(row=sheet_row_index, column=20).value) + "'")
        else:
            insert_project_finance4.append("NULL")
        # extend_title
        insert_project_finance4.append("'项目进展'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=21).value:
            insert_project_finance4.append("'http://47.116.71.76:23349/upload/bo/pic/" +
                                           str(sheet.cell(row=sheet_row_index, column=21).value).split(',')[0] + "'")
        else:
            insert_project_finance4.append("NULL")

        # image_url
        insert_project_finance4.append("1")

        # insert_project_finance4 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print(
                    '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                    + ','.join(insert_project_finance4) + ");")
        # endregion 4、项目进展

        # region 5、联营模式
        insert_project_finance5 = []

        # creator
        insert_project_finance5.append("0")
        # create_time
        insert_project_finance5.append("NOW()")
        # modifier
        insert_project_finance5.append("NULL")
        # modify_time
        insert_project_finance5.append("NULL")
        # delete_flag
        insert_project_finance5.append("0")
        # remark
        insert_project_finance5.append("NULL")
        # project_id
        insert_project_finance5.append(bo_project_id)
        # finance_config_id
        insert_project_finance5.append("5")
        # content
        if sheet.cell(row=sheet_row_index, column=14).value:
            insert_project_finance5.append("'" + str(sheet.cell(row=sheet_row_index, column=14).value) + "'")
        else:
            insert_project_finance5.append("NULL")
        # extend_title
        insert_project_finance5.append("'联营模式'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=15).value:
            insert_project_finance5.append("'http://47.116.71.76:23349/upload/bo/pic/" +
                                           str(sheet.cell(row=sheet_row_index, column=15).value).split(',')[0] + "'")
        else:
            insert_project_finance5.append("NULL")

        # image_url
        insert_project_finance5.append("1")

        # insert_project_finance5 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print(
                    '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                    + ','.join(insert_project_finance5) + ");")
        # endregion 5、联营模式

        # region 6、联营收益
        insert_project_finance6 = []

        # creator
        insert_project_finance6.append("0")
        # create_time
        insert_project_finance6.append("NOW()")
        # modifier
        insert_project_finance6.append("NULL")
        # modify_time
        insert_project_finance6.append("NULL")
        # delete_flag
        insert_project_finance6.append("0")
        # remark
        insert_project_finance6.append("NULL")
        # project_id
        insert_project_finance6.append(bo_project_id)
        # finance_config_id
        insert_project_finance6.append("6")
        # content
        if sheet.cell(row=sheet_row_index, column=16).value:
            insert_project_finance6.append("'" + str(sheet.cell(row=sheet_row_index, column=16).value) + "'")
        else:
            insert_project_finance6.append("NULL")
        # extend_title
        insert_project_finance6.append("'联营收益'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=17).value:
            insert_project_finance6.append("'http://47.116.71.76:23349/upload/bo/pic/" +
                                           str(sheet.cell(row=sheet_row_index, column=17).value).split(',')[0] + "'")
        else:
            insert_project_finance6.append("NULL")

        # image_url
        insert_project_finance6.append("1")

        # insert_project_finance6 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print(
                '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                + ','.join(insert_project_finance6) + ");")
        # endregion 6、联营收益

        # region 7、行业前景
        insert_project_finance7 = []

        # creator
        insert_project_finance7.append("0")
        # create_time
        insert_project_finance7.append("NOW()")
        # modifier
        insert_project_finance7.append("NULL")
        # modify_time
        insert_project_finance7.append("NULL")
        # delete_flag
        insert_project_finance7.append("0")
        # remark
        insert_project_finance7.append("NULL")
        # project_id
        insert_project_finance7.append(bo_project_id)
        # finance_config_id
        insert_project_finance7.append("7")
        # content
        if sheet.cell(row=sheet_row_index, column=10).value:
            insert_project_finance7.append("'" + str(sheet.cell(row=sheet_row_index, column=10).value) + "'")
        else:
            insert_project_finance7.append("NULL")
        # extend_title
        insert_project_finance7.append("'行业前景'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=11).value:
            insert_project_finance7.append("'http://47.116.71.76:23349/upload/bo/pic/" +
                                           str(sheet.cell(row=sheet_row_index, column=11).value).split(',')[0] + "'")
        else:
            insert_project_finance7.append("NULL")

        # image_url
        insert_project_finance7.append("1")

        # insert_project_finance7 = '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
        print(
                '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                + ','.join(insert_project_finance7) + ");")
        # endregion 7、行业前景

        # region 8、收益预测
        insert_project_finance8 = []

        # creator
        insert_project_finance8.append("0")
        # create_time
        insert_project_finance8.append("NOW()")
        # modifier
        insert_project_finance8.append("NULL")
        # modify_time
        insert_project_finance8.append("NULL")
        # delete_flag
        insert_project_finance8.append("0")
        # remark
        insert_project_finance8.append("NULL")
        # project_id
        insert_project_finance8.append(bo_project_id)
        # finance_config_id
        insert_project_finance8.append("8")
        # content
        if sheet.cell(row=sheet_row_index, column=18).value:
            insert_project_finance8.append("'" + str(sheet.cell(row=sheet_row_index, column=18).value) + "'")
        else:
            insert_project_finance8.append("NULL")
        # extend_title
        insert_project_finance8.append("'收益预测'")
        # image_url
        if sheet.cell(row=sheet_row_index, column=18).value:
            insert_project_finance8.append("'http://47.116.71.76:23349/upload/bo/pic/" +
                                           str(sheet.cell(row=sheet_row_index, column=18).value).split(',')[0] + "'")
        else:
            insert_project_finance8.append("NULL")

        # image_url
        insert_project_finance8.append("1")

        print(
                '''INSERT INTO `mallcloud`.`bo_project_finance`(`creator`, `create_time`, `modifier`, `modify_time`, `delete_flag`, `remark`, `project_id`, `finance_config_id`, `content`, `extend_title`, `image_url`, `is_show`) VALUES ('''
                + ','.join(insert_project_finance8) + ");")
        # endregion 8、收益预测

        # endregion bo_project_finance sql语句生成



        #endregion sql语句生成

analysisile()
print('success')
